/** <a href="http://www.cpupk.com/decompiler">Eclipse Class Decompiler</a> plugin, Copyright (c) 2017 Chen Chao. **/
package com.minierp.dao.hibernate;

import com.minierp.bean.UserBean;
import com.minierp.dao.CooperBillDao;
import com.minierp.dao.hibernate.BaseDaoImpl;
import com.minierp.helper.EncCommonHelper;
import com.minierp.model.CooperBill;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

public class CooperBillDaoImpl extends BaseDaoImpl implements CooperBillDao {
	private String getBlearSearchHql(String searchKey) {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.billCode like :billCode)  or (e.estName like :estName) or (e.nameSpell like :nameSpell) or (b.content like :content))";
		}

		return str;
	}

	private void setBlearSearchValue(Query q, String searchKey) {
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			q.setString("billCode", "%" + searchKey + "%");
			q.setString("estName", "%" + searchKey + "%");
			q.setString("nameSpell", "%" + searchKey + "%");
			q.setString("content", "%" + searchKey + "%");
		}

	}

	public CooperBill get(String billCode) {
		String sql = "select b from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be where b.id=be.id and b.billCode = :billCode and be.enabled=\'Y\' order by b.id";
		String[] paramNames = new String[]{"billCode"};
		Object[] values = new Object[]{billCode};
		List list = this.getHibernateTemplate().findByNamedParam(sql,
				paramNames, values);
		return list.isEmpty() ? null : (CooperBill) list.get(0);
	}

	public Object[] getStatusValues(long id) {
		String sql = "select b.updateTime,b.imprest,b.cooperDate,b.bankAccountId,b.cooperatorId from com.minierp.model.CooperBill b where b.id="
				+ id;
		List list = this.getHibernateTemplate().find(sql);
		return list.size() > 0 ? (Object[]) list.get(0) : null;
	}

	public List<?> getListForExamine(String searchKey, long tsId,
			long estManagerId, UserBean ub, Date allowStart, int startIndex,
			int num) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.cooperDate>=:allowStart";
		}

		String sql = "select b,e.estName from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'N\'"
				+ " order by b.id desc,b.billCode desc";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public int getListForExamineCount(String searchKey, long tsId,
			long estManagerId, UserBean ub, Date allowStart) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.cooperDate>=:allowStart";
		}

		String sql = "select count(b.id) from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str + " and be.enabled=\'Y\'" + " and be.examined=\'N\'";
		Session sess = null;

		int arg13;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg13 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public List<?> getListForUnExamine(String searchKey, long tsId,
			long estManagerId, UserBean ub, Date allowStart, int startIndex,
			int num) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.cooperDate>=:allowStart";
		}

		String sql = "select b,e.estName from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.hasGather=\'N\'"
				+ " and be.hasPay=\'N\'"
				+ " and be.hasAdjust=\'N\'"
				+ " order by b.id desc,b.billCode desc";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public int getListForUnExamineCount(String searchKey, long tsId,
			long estManagerId, UserBean ub, Date allowStart) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.cooperDate>=:allowStart";
		}

		String sql = "select count(b.id) from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.hasPay=\'N\'"
				+ " and be.hasAdjust=\'N\'"
				+ " and be.hasGather=\'N\'";
		Session sess = null;

		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (!list.isEmpty()) {
				int arg13 = Integer.parseInt(list.get(0).toString());
				return arg13;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	public List<?> getListForGather(String gathered, String timeout,
			String searchKey, long tsId, UserBean ub, long estManagerId,
			Date allowStart, int startIndex, int num) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (gathered != null && !gathered.equals("")) {
			str = str + " and be.gathered=:gathered";
		} else {
			str = str + " and be.gathered!=\'Y\'";
		}

		if (timeout != null && timeout.equals("Y")) {
			str = str + " and b.gatherDate<:gatherDate";
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.cooperDate>=:allowStart";
		}

		String sql = "select b,e.estName,be.hasGather from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " order by b.id desc,b.billCode desc";
		Session sess = null;

		List arg16;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (gathered != null && !gathered.equals("")) {
				q.setString("gathered", gathered);
			}

			if (timeout != null && timeout.equals("Y")) {
				q.setTimestamp("gatherDate", new Date());
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg16 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg16;
	}

	public int getListForGatherCount(String gathered, String timeout,
			String searchKey, long tsId, UserBean ub, long estManagerId,
			Date allowStart) throws Exception {
		String str = this.getBlearSearchHql(searchKey);
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (gathered != null && !gathered.equals("")) {
			str = str + " and be.gathered=:gathered";
		} else {
			str = str + " and be.gathered!=\'Y\'";
		}

		if (timeout != null && timeout.equals("Y")) {
			str = str + " and b.gatherDate<:gatherDate";
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.cooperDate>=:allowStart";
		}

		String sql = "select count(b.id) from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str + " and be.enabled=\'Y\'" + " and be.examined=\'Y\'";
		Session sess = null;

		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, searchKey);
			if (gathered != null && !gathered.equals("")) {
				q.setString("gathered", gathered);
			}

			if (timeout != null && timeout.equals("Y")) {
				q.setTimestamp("gatherDate", new Date());
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (!list.isEmpty()) {
				int arg15 = Integer.parseInt(list.get(0).toString());
				return arg15;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	public List<?> getListForPay(long cooperatorId, long tallySetId,
			Date startDate, Date endDate) {
		String str = "";
		if (startDate != null) {
			str = str + " and b.cooperDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.cooperDate<:endDate";
		}

		String sql = "select b,be from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be where b.id=be.id and b.cooperatorId="
				+ cooperatorId
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.paied=\'N\'"
				+ " and b.tallySetId=" + tallySetId + " order by b.cooperDate";
		Session sess = null;

		List arg11;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			arg11 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg11;
	}

	public List<?> getList(long cooperatorId, boolean examined, UserBean ub) {
		String examineFlag = "N";
		if (examined) {
			examineFlag = "Y";
		}

		String sql = "select b from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be where b.id=be.id and b.cooperatorId="
				+ cooperatorId
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'"
				+ examineFlag
				+ "\'"
				+ EncCommonHelper
						.createTallySetSQLStr("b.tallySetId", ub, true)
				+ " order by b.cooperDate desc,b.billCode desc";
		return this.getHibernateTemplate().find(sql);
	}

	public List<?> getListForPay(String searchKey, Date startDate,
			Date endDate, String billCode, String content, String minCost,
			String maxCost, long pmId, long tsId, long estManagerId,
			UserBean ub, int startIndex, int num) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((e.estCode like :estCode)  or (e.estName like :estName))";
		}

		if (billCode != null) {
			billCode = billCode.trim();
		}

		if (billCode != null && !billCode.equals("")) {
			str = str + " and (b.billCode like :billCode)";
		}

		if (content != null) {
			content = content.trim();
		}

		if (content != null && !content.equals("")) {
			str = str + " and (b.content like :content)";
		}

		if (minCost != null && !minCost.equals("")) {
			str = str + " and (b.cost-be.paiedCost+be.adjustCost)>=" + minCost;
		}

		if (maxCost != null && !maxCost.equals("")) {
			str = str + " and (b.cost-be.paiedCost+be.adjustCost)<" + maxCost;
		}

		if (startDate != null) {
			str = str + " and b.cooperDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.cooperDate<:endDate";
		}

		if (pmId != 0L) {
			str = str + " and b.payModeId=" + pmId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		String sql = "select b,e.estName,be.paiedCost,be.adjustCost from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.paied=\'N\'" + " order by b.cooperDate desc";
		Session sess = null;

		List arg21;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("estCode", "%" + searchKey + "%");
				q.setString("estName", "%" + searchKey + "%");
			}

			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (billCode != null && !billCode.equals("")) {
				q.setString("billCode", "%" + billCode + "%");
			}

			if (content != null && !content.equals("")) {
				q.setString("content", "%" + content + "%");
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg21 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg21;
	}

	public List getListForPayCount(String searchKey, Date startDate,
			Date endDate, String billCode, String content, String minCost,
			String maxCost, long pmId, long tsId, long estManagerId, UserBean ub)
			throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((e.estCode like :estCode)  or (e.estName like :estName))";
		}

		if (billCode != null) {
			billCode = billCode.trim();
		}

		if (billCode != null && !billCode.equals("")) {
			str = str + " and (b.billCode like :billCode)";
		}

		if (content != null) {
			content = content.trim();
		}

		if (content != null && !content.equals("")) {
			str = str + " and (b.content like :content)";
		}

		if (minCost != null && !minCost.equals("")) {
			str = str + " and (b.cost-be.paiedCost+be.adjustCost)>=" + minCost;
		}

		if (maxCost != null && !maxCost.equals("")) {
			str = str + " and (b.cost-be.paiedCost+be.adjustCost)<" + maxCost;
		}

		if (startDate != null) {
			str = str + " and b.cooperDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.cooperDate<:endDate";
		}

		if (pmId != 0L) {
			str = str + " and b.payModeId=" + pmId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		String sql = "select count(b.id),sum(b.cost),sum(be.paiedCost),sum(be.adjustCost) from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.paied=\'N\'";
		Session sess = null;

		List arg19;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("estCode", "%" + searchKey + "%");
				q.setString("estName", "%" + searchKey + "%");
			}

			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (billCode != null && !billCode.equals("")) {
				q.setString("billCode", "%" + billCode + "%");
			}

			if (content != null && !content.equals("")) {
				q.setString("content", "%" + content + "%");
			}

			arg19 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg19;
	}

	public BigDecimal getSmsImprest(Date startDate, Date endDate)
			throws Exception {
		String sql = "select sum(b.imprest) from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be where b.id=be.id and be.enabled=\'Y\' and be.examined=\'Y\' and b.cooperDate>=:startDate and b.cooperDate<:endDate";
		Session sess = null;

		BigDecimal arg7;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			List list = q.list();
			if (!list.isEmpty() && list.get(0) != null) {
				arg7 = new BigDecimal(list.get(0).toString());
				return arg7;
			}

			arg7 = new BigDecimal(0);
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg7;
	}

	public List<?> getSmsGatherList(Date lastSmsDate) throws Exception {
		String sql = " select c.contact_name,c.mobile,e.est_name,b.id,b.bill_code,b.content from cooper_bill b,cooper_bill_ext be,establishment e,establishment_contact c where b.id=be.id and b.cooperator_id=e.id and e.id=c.est_id and be.enabled=\'Y\' and be.examined=\'Y\' and be.gathered=\'N\' and (c.mobile is not null) and c.mobile!=\'\' and c.is_main=\'Y\' and ((be.sms_time is null) or be.sms_time<:sms_time) and b.gather_date<:gather_date";
		Session sess = null;

		List arg5;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			q.setTimestamp("sms_time", lastSmsDate);
			q.setTimestamp("gather_date", new Date());
			arg5 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg5;
	}

	public int update(final long id, final Date lastSmsTime) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String sql = "update cooper_bill_ext set sms_time=:sms_time where id="
								+ id;
						return new Integer(session.createSQLQuery(sql)
								.setTimestamp("sms_time", lastSmsTime)
								.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List<?> getMonthCost(Date startDate, Date endDate, long tsId,
			UserBean ub, long managerId, Date allowStart) throws Exception {
		String str = "";
		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		if (managerId != 0L) {
			str = str + " and e.manager_id=" + managerId;
		}

		String sql = "select month(b.cooper_date),sum(b.cost),sum(be.adjust_cost),sum(be.paied_cost) from cooper_bill b,cooper_bill_ext be ,establishment e where b.id=be.id and e.id=b.cooperator_id and be.enabled=\'Y\' and be.examined=\'Y\'"
				+ str
				+ " and b.cooper_date>=:startDate"
				+ " and b.cooper_date<:endDate"
				+ " group by month(b.cooper_date)";
		Session sess = null;

		List arg13;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (allowStart != null && allowStart.after(startDate)) {
				q.setDate("startDate", allowStart);
			} else {
				q.setDate("startDate", startDate);
			}

			q.setDate("endDate", endDate);
			arg13 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public BigDecimal getCost(long tsId, UserBean ub, Date startDate,
			Date endDate) throws Exception {
		String str = "";
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select sum(b.cost) from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be where b.id=be.id and be.enabled=\'Y\' and be.examined=\'Y\' and b.cooperDate>=:startDate and b.cooperDate<:endDate"
				+ str;
		Session sess = null;

		BigDecimal arg11;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			List list = q.list();
			if (list.isEmpty() || list.get(0) == null) {
				arg11 = new BigDecimal(0);
				return arg11;
			}

			arg11 = new BigDecimal(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg11;
	}

	public List<?> getListByBusinessBill(long billId) {
		String sql = "select distinct b,be.examined from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be ,com.minierp.model.CooperBillItem i ,com.minierp.model.BusinessBillItem bi where b.id=i.billId and b.id=be.id and be.enabled=\'Y\' and i.bbItemId=bi.id and bi.billId="
				+ billId + " order by b.cooperDate";
		return this.getHibernateTemplate().find(sql);
	}

	public List<?> getGathListByBusinessBill(long billId) {
		String sql = "select distinct b from com.minierp.model.GatherBill b ,com.minierp.model.GatherBillItem i ,com.minierp.model.CooperBillItem ci ,com.minierp.model.BusinessBillItem bi where b.id=i.billId and i.cbItemId=ci.id and ci.bbItemId=bi.id and b.enabled=\'Y\' and bi.billId="
				+ billId + " order by b.gatherDate";
		return this.getHibernateTemplate().find(sql);
	}

	public List<?> getListForAccountPay(long cooperatorId, Date startDate,
			Date endDate, long tallySetId) {
		String sql = "select b,be from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be where b.id=be.id and b.cooperatorId="
				+ cooperatorId
				+ " and be.enabled=\'Y\'"
				+ " and be.examined=\'Y\'"
				+ " and be.paied=\'N\'"
				+ " and b.tallySetId=" + tallySetId;
		if (startDate != null) {
			sql = sql + " and b.cooperDate>=:startDate";
		}

		if (endDate != null) {
			sql = sql + " and b.cooperDate<:endDate";
		}

		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public boolean isHasBill(long cooperatorId) {
		String sql = "select b.id from cooper_bill b,cooper_bill_ext be where b.id=be.id and be.enabled=\'Y\' and b.cooperator_id="
				+ cooperatorId + " limit 1";
		Session sess = null;

		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			List list = q.list();
			if (list.size() > 0) {
				return true;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return false;
	}

	public int estCorporate(final long fromClientId, final long toClientId) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.CooperBill set cooperatorId="
								+ toClientId
								+ " ,updateTime=:updateTime"
								+ " where cooperatorId=" + fromClientId;
						Query q = session.createQuery(hql);
						q.setTimestamp("updateTime", new Date());
						return new Integer(q.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List getBaImprest(long tsId, UserBean ub, Date startDate,
			Date endDate) throws Exception {
		String str = "";
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select sum(b.imprest),b.bankAccountId from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be where b.id=be.id and be.enabled=\'Y\' and be.examined=\'Y\' and b.cooperDate>=:startDate and b.cooperDate<:endDate"
				+ str + " group by b.bankAccountId";
		Session sess = null;

		List arg10;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			arg10 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg10;
	}

	public List getAppListCount(Date startDate, Date endDate, String estName,
			String exam, String paie, long tsId, UserBean ub, long mngId)
			throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.cooperDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.cooperDate<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.estName like :estName)";
		}

		if (exam != null && !exam.equals("")) {
			str = str + " and be.examined=:exam";
		}

		if (paie != null && !paie.equals("")) {
			str = str + " and be.paied=:paie";
		}

		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(*),sum(b.cost),sum(b.cost-be.paiedCost+be.adjustCost) from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str + " and be.enabled=\'Y\'";
		Session sess = null;

		List arg15;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			if (exam != null && !exam.equals("")) {
				q.setString("exam", exam);
			}

			if (paie != null && !paie.equals("")) {
				q.setString("paie", paie);
			}

			arg15 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg15;
	}

	public List getAppList(Date startDate, Date endDate, String estName,
			String exam, String paie, long tsId, UserBean ub, long mngId,
			String orde, int startIndex, int num) throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.cooperDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.cooperDate<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.estName like :estName)";
		}

		if (exam != null && !exam.equals("")) {
			str = str + " and be.examined=:exam";
		}

		if (paie != null && !paie.equals("")) {
			str = str + " and be.paied=:paie";
		}

		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String ordeStr = " order by b.cooperDate desc,b.billCode desc";
		if (orde != null) {
			if (orde.equals("COOP_DATE")) {
				ordeStr = " order by b.cooperDate desc,b.billCode desc";
			} else if (orde.equals("COOP_COST")) {
				ordeStr = " order by b.cost desc,b.billCode desc";
			} else if (orde.equals("BILL_CODE")) {
				ordeStr = " order by b.billCode desc";
			} else if (orde.equals("COOP_CONT")) {
				ordeStr = " order by b.content desc,b.billCode desc";
			} else if (orde.equals("ESTA_NAME")) {
				ordeStr = " order by e.nameSpell desc,b.billCode desc";
			}
		}

		String sql = "select b,e.estName,be from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str + " and be.enabled=\'Y\'" + ordeStr;
		Session sess = null;

		List arg19;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			if (exam != null && !exam.equals("")) {
				q.setString("exam", exam);
			}

			if (paie != null && !paie.equals("")) {
				q.setString("paie", paie);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg19 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg19;
	}

	public List getAppListForExamCount(String sk, long tsId, UserBean ub,
			Date allowStart, long mngId) throws Exception {
		String str = this.getBlearSearchHql(sk);
		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and b.cooperDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(*),sum(b.cost) from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str + " and be.examined=\'N\'" + " and be.enabled=\'Y\'";
		Session sess = null;

		List arg12;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, sk);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			arg12 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg12;
	}

	public List getAppListForExam(String sk, long tsId, UserBean ub,
			Date allowStart, long mngId, int startIndex, int num)
			throws Exception {
		String str = this.getBlearSearchHql(sk);
		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and b.cooperDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select b,e.estName from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str
				+ " and be.examined=\'N\'"
				+ " and be.enabled=\'Y\'"
				+ " order by b.cooperDate desc,b.billCode desc";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, sk);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public int getAppListForUnExamCount(String sk, long tsId, UserBean ub,
			Date allowStart, long mngId) throws Exception {
		String str = this.getBlearSearchHql(sk);
		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and b.cooperDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(*) from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str
				+ " and be.examined=\'Y\'"
				+ " and be.enabled=\'Y\'"
				+ " and be.hasPay=\'N\'"
				+ " and be.hasGather=\'N\'"
				+ " and be.hasAdjust=\'N\'";
		Session sess = null;

		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, sk);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (!list.isEmpty() && list.get(0) != null) {
				int arg13 = Integer.parseInt(list.get(0).toString());
				return arg13;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	public List getAppListForUnExam(String sk, long tsId, UserBean ub,
			Date allowStart, long mngId, int startIndex, int num)
			throws Exception {
		String str = this.getBlearSearchHql(sk);
		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and b.cooperDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select b,e.estName,be.examinerName,be.examineTime from com.minierp.model.CooperBill b,com.minierp.model.CooperBillExt be,com.minierp.model.Establishment e where b.id=be.id and b.cooperatorId=e.id"
				+ str
				+ " and be.examined=\'Y\'"
				+ " and be.enabled=\'Y\'"
				+ " and be.hasPay=\'N\'"
				+ " and be.hasGather=\'N\'"
				+ " and be.hasAdjust=\'N\'" + " order by be.examineTime desc";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			this.setBlearSearchValue(q, sk);
			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}
}